package com.ruoyi.aitcommon.api;

import java.util.*;

import com.ruoyi.aitcommon.service.SQLService;
import com.ruoyi.aitcommon.utils.*;
import com.ruoyi.common.utils.ServletUtils;
import com.ruoyi.common.utils.ip.IpUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.aitcommon.anotation.ApiLog;
import com.ruoyi.aitcommon.config.ConfigManager;
import com.ruoyi.aitcommon.vo.DBConVO;

import cn.hutool.core.util.StrUtil;

@RestController
@RequestMapping("/ait/common/dboper")
public class DBOperation {
	@Autowired
	ConfigManager configManager;
	@Autowired
	SQLService sqlService;
	/**
	 * <pre>
	 * AIT通用查询接口
	 * 参数json结构： 
	{
	"table": "ait_log", //表名 or 视图名,建议使用视图
	"path": "id=11436",
	"db" : "config.aitconfig.datasource.aitdb"
	}
	 * 返回json结构：
	{
	"msg": "操作成功",
	"code": 200,
	"data": [
		{
			"id": 11436,
			"log_type": "服务日志",
	        "oper_ip": "",
	        "oper_url": "",
	        "req_method": "",
	        ....
		}
	]
	}
	 * </pre>
	 * 
	 * 提供跨库的通用查询
	 * 
	 * @param jsonstr
	 * @return 查询结果
	 * 
	 */
	@ApiLog("AIT通用查询接口")
	@PostMapping("/AITQuery")
	public JSONObject AITQuery(@RequestBody String jsonstr) {
		List<Map<String, Object>> retlist = null;
		try {
//			参数校验
			ExceptionUtils.checkBlankOrUndefined(jsonstr, "传入参数为空");
			JSONObject json = JSONObject.parseObject(jsonstr);
			String table = json.getString("table");
			ExceptionUtils.checkBlankOrUndefined(table, "table为空");
//			合法性验证
			Validate(table);
//            获取DB数据集合
			String path = json.getString("path");
			String dbstr = json.getString("db");
			retlist = sqlService.getDBData(dbstr,table, path);
//			返回数据处理
//			按照U9风格,构造头体结构
			retlist = getU9Maps(retlist, table);
			return JSONResult.success(retlist);
		} catch (Exception ex) {
			return JSONResult.error(ex.getMessage());
		}
	}

	/**
	 * 将Map转化为U9头体格式
	 * 需要在config.authconfig.AITQuery.tableConfig中进行配置
	 * @param mapList
	 * @param table
	 * @return
	 * @throws Exception
	 */
	private List<Map<String, Object>> getU9Maps(List<Map<String, Object>> mapList, String table) throws Exception {
		final Object tableConfig = configManager.getContextByParm("config.authconfig.AITQuery.tableConfig");
		if(tableConfig!=null){
			Map<String,Object> tableMap=(Map)tableConfig;
			if(tableMap.containsKey(table)){
				final Object fenggeObj = MapUtils.getPathObject(table + ".fengge", tableMap);
				final String zuhe = MapUtils.getPathString(table + ".zuhe", tableMap);
				if(ObjUtils.isNotBlank(fenggeObj)){
					List<Map<String,Object>> fengge=(List<Map<String,Object>>)fenggeObj;
					mapList =ListMapUtils.transU9Map(mapList, fengge,zuhe);
				}
			}
		}
		return mapList;
	}

	/**
	 * 基于表名,进行合法性检查
	 * @param table 表名
	 * @throws Exception
	 */
	private void Validate(String table) throws Exception {
		final Object authobj = configManager.getContextByParm("config.authconfig.AITQuery");
		ExceptionUtils.checkBlankOrUndefined(authobj, "config.authconfig.AITQuery配置为空");
		JSONObject auth=(JSONObject)authobj;
		JSONArray authtables=auth.getJSONArray("tables");
		ExceptionUtils.checkBlankOrUndefined(authtables, "鉴权配置中tables配置为空");
		if(!authtables.contains(table)){
			ExceptionUtils.throwException(null,"鉴权配置中tables不包含[{}]", table);
		}
		final JSONArray clientIPS = auth.getJSONArray("clientIP");
		if(clientIPS!=null&&clientIPS.size()>0){
			final String ipAddr = IpUtils.getIpAddr(ServletUtils.getRequest());
			if(!clientIPS.contains(ipAddr)){
				ExceptionUtils.throwException(null,"鉴权配置中clientIP不包含[{}]",ipAddr);
			}
		}
	}

	/**
	 * 数据库通用更新接口
	 * 
	 * <pre>
	 * 参数JSON结构
	{
	"sql": "UPDATE ait_data_mapping set name='编码01' where id=200",
	"db" : "config.aitconfig.datasource.aitdb"
	}
	 * 
	 * 返回JSON结构
	{
	"msg": "更新记录数：1",
	"code": 200,
	"data": 1
	}
	 * 
	 * </pre>
	 * 
	 * @param jsonstr
	 * @return
	 */
	@ApiLog("数据库通用更新接口")
	@PostMapping("/DBUpdate")
	public JSONObject commonUpdate(@RequestBody String jsonstr) {
		try {
			ExceptionUtils.checkBlankOrUndefined(jsonstr, "传入参数为空");
			JSONObject json = JSONObject.parseObject(jsonstr);
			String sqlstr = json.getString("sql");
			ExceptionUtils.checkBlankOrUndefined(sqlstr, "sql为空！");
			String dbstr = json.getString("db");
			ExceptionUtils.checkBlankOrUndefined(dbstr, "db为空！");
			JSONObject dbjson = (JSONObject) configManager.getContextByParm(dbstr);

//			构造SQL并运行
			DBConVO db1 = JSONObject.toJavaObject(dbjson, DBConVO.class);
			int ret = SQLUtils.execUpdate(sqlstr, db1);

			return JSONResult.success("更新记录数：" + ret, ret);
		} catch (Exception ex) {
			return JSONResult.error(ex.getMessage());
		}
	}

	/**
	 * 数据库通用批量更新接口
	 * 
	 * <pre>
	 * 参数json结构：
	{
	"sqls": [
			"UPDATE ait_data_mapping set name='编码01' where id=200",
			"UPDATE ait_data_mapping set name='编码01' where id=200"
	],
	"db" : "config.aitconfig.datasource.aitdb"
	}
	 * 
	 * 返回json结构：
	{
	"msg": "更新记录数：2",
	"code": 200,
	"data": 2
	}
	 * </pre>
	 * 
	 * @param jsonstr
	 * @return
	 */
	@ApiLog("数据库通用批量更新接口")
	@PostMapping("/DBBatchUpdate")
	public JSONObject commonBatchUpdate(@RequestBody String jsonstr) {
		try {
			ExceptionUtils.checkBlankOrUndefined(jsonstr, "传入参数为空");
			JSONObject json = JSONObject.parseObject(jsonstr);
			JSONArray sqls = json.getJSONArray("sqls");
			if (sqls == null || sqls.size() == 0)
				throw new Exception("sqls为空");
			String dbstr = json.getString("db");
			ExceptionUtils.checkBlankOrUndefined(dbstr, "db为空！");
			JSONObject dbjson = (JSONObject) configManager.getContextByParm(dbstr);

//			构造SQL并运行
			DBConVO db1 = JSONObject.toJavaObject(dbjson, DBConVO.class);
			List<String> sqls1 = new ArrayList<String>();
			for (Object sql : sqls) {
				sqls1.add(sql.toString());
			}
			int ret = SQLUtils.execUpdate(sqls1, db1);
			return JSONResult.success("更新记录数：" + ret, ret);
		} catch (Exception ex) {
			return JSONResult.error(ex.getMessage());
		}
	}

	private Map<String, Object> parmVerify(String jsonstr) throws Exception {
		Map<String, Object> retmap = new HashMap<String, Object>();

		ExceptionUtils.checkBlankOrUndefined(jsonstr, "传入参数为空");

		JSONObject json = JSONObject.parseObject(jsonstr);
		Object dmap = MapUtils.getPathObject("data", json);
		if (!(dmap instanceof List))
			throw new Exception("data必须是数组！");
		retmap.put("data", dmap);

		JSONObject cxt = json.getJSONObject("context");
		ExceptionUtils.checkBlankOrUndefined(cxt, "context 对象为空");
		retmap.put("context", cxt);

		ExceptionUtils.checkBlankOrUndefined(cxt.getString("db"), "db链接为空");
		ExceptionUtils.checkBlankOrUndefined(cxt.getString("tablename"), "tablename为空");

		return retmap;
	}

	/**
	 * 数据库通用插入接口
	 * 
	 * <pre>
	 * 参数JSON结构
	{
	"data": [
		{
			"code": "",
			"import_id": "interface_distributor_return_20210714_202126261819799101",
			"name": "KS11000036"
		},
		{
			"code": "",
			"import_id": "interface_distributor_return_20210714_202126261819799101",
			"name": "000002"
		}
	],
	"context": {
		"tablename": "interface_distributor_return",
		"db" : "config.aitconfig.datasource.aitdb"
	}
	}
	 * 返回JSON结构
	{
	"msg": "操作成功",
	"code": 200,
	"data": 2
	}
	 * </pre>
	 * 
	 * @param jsonstr
	 * @return
	 */
	@ApiLog("数据库Map插入接口")
	@PostMapping("/mapInsert")
	public JSONObject mapInsert(@RequestBody String jsonstr) {
		try {
			Map<String, Object> map = this.parmVerify(jsonstr);
			Object data = map.get("data");

//			构造DB链接
			JSONObject cxt = (JSONObject) map.get("context");
			Object dbobj = cxt.get("db");
			JSONObject dbjson = (JSONObject) configManager.getContextByParm(dbobj.toString());
			DBUtils db = new DBUtils(dbjson);
//			构造SQL并运行
			List<String> sqls = SQLUtils.ListMap2Insert((List<Map<String, Object>>) data, cxt.getString("tablename"),
					cxt.getString("includes"), cxt.getString("excludes"), db.dbCon);
			int ret = SQLUtils.execUpdate(sqls, db);

			return JSONResult.success(ret);
		} catch (Exception ex) {
			return JSONResult.error(ex.getMessage());
		}
	}

	@ApiLog("数据库Map更新接口")
	@PostMapping("/mapUpdate")
	public JSONObject mapUpdate(@RequestBody String jsonstr) {
		try {
			Map<String, Object> map = this.parmVerify(jsonstr);
			Object data = map.get("data");

//			构造DB链接
			JSONObject cxt = (JSONObject) map.get("context");
			Object dbobj = cxt.get("db");
			JSONObject dbjson = (JSONObject) configManager.getContextByParm(dbobj.toString());
			DBUtils db = new DBUtils(dbjson);
			final String tablename = cxt.getString("tablename");
			final String key = cxt.getString("key");
			final String includes = cxt.getString("includes");
			final String excludes = cxt.getString("excludes");
//			构造SQL并运行
			List<String> sqls = SQLUtils.ListMap2Update((List<Map<String, Object>>) data, tablename
					, Arrays.asList(key), includes, excludes, db.dbCon);
			int ret = SQLUtils.execUpdate(sqls, db);

			return JSONResult.success(ret);
		} catch (Exception ex) {
			return JSONResult.error(ex.getMessage());
		}
	}

	@ApiLog("数据库Map删除接口")
	@PostMapping("/mapDelete")
	public JSONObject mapDelete(@RequestBody String jsonstr) {
		try {
			Map<String, Object> map = this.parmVerify(jsonstr);
			Object data = map.get("data");
//			构造DB链接
			JSONObject cxt = (JSONObject) map.get("context");
			Object dbobj = cxt.get("db");
			JSONObject dbjson = (JSONObject) configManager.getContextByParm(dbobj.toString());
			DBUtils db = new DBUtils(dbjson);
			final String tablename = cxt.getString("tablename");
			final String key = cxt.getString("key");
//			构造SQL并运行
			List<String> sqls = SQLUtils.ListMap2Delete((List<Map<String, Object>>) data, tablename, key);
			int ret = SQLUtils.execUpdate(sqls, db);

			return JSONResult.success(ret);
		} catch (Exception ex) {
			return JSONResult.error(ex.getMessage());
		}
	}

}
